﻿Imports System.Data.SqlClient
Public Class dbPriceCalc
    Dim Var As New varDeclare

    Public Function GetBaseList(Code As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM vPriceCalc WHERE Code = ISNULL(@code, Code)"
        If (Code = "") Then
            command.Parameters.Add("@code", SqlDbType.VarChar).Value = DBNull.Value
        Else
            command.Parameters.Add("@code", SqlDbType.VarChar).Value = Code
        End If
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetItemDetail(Code As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM PRICE_CALC WHERE GROUPS+'-'+CODE = ISNULL(@code, Code)"
        If (Code = "") Then
            command.Parameters.Add("@code", SqlDbType.VarChar).Value = DBNull.Value
        Else
            command.Parameters.Add("@code", SqlDbType.VarChar).Value = Code
        End If
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function DoPriceCalculate(Principal_Code As String, Groups As String, Code As String, Curr_base As String, _
                                     CHG As String, Percent_0 As String, Percent_1 As String, Percent_2 As String, _
                                     Percent_3 As String, Exworks As String, CHG_CURR As String, EXW_CURR As String, _
                                     JKT_CURR As String, SIN_CURR As String, OTH_CURR As String, PR_CURR4 As String, _
                                     F As String, F1 As String, JKT As String, SIN As String, OTH As String, PR As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "sp_PriceCalcTemp"
        command.Parameters.Add("@Principal_Code", SqlDbType.VarChar).Value = Principal_Code
        command.Parameters.Add("@Groups", SqlDbType.VarChar).Value = Groups
        command.Parameters.Add("@Code", SqlDbType.VarChar).Value = Code
        command.Parameters.Add("@Curr_base", SqlDbType.VarChar).Value = Curr_base
        If (CHG <> "") Then
            command.Parameters.Add("@CHG", SqlDbType.Decimal).Value = Convert.ToDecimal(CHG)
        End If
        If (Percent_0 <> "") Then
            command.Parameters.Add("@Percent_0", SqlDbType.Decimal).Value = Convert.ToDecimal(Percent_0)
        End If
        If (Percent_1 <> "") Then
            command.Parameters.Add("@Percent_1", SqlDbType.Decimal).Value = Convert.ToDecimal(Percent_1)
        End If
        If (Percent_2 <> "") Then
            command.Parameters.Add("@Percent_2", SqlDbType.Decimal).Value = Convert.ToDecimal(Percent_2)
        End If
        If (Percent_3 <> "") Then
            command.Parameters.Add("@Percent_3", SqlDbType.Decimal).Value = Convert.ToDecimal(Percent_3)
        End If
        If (Exworks <> "") Then
            command.Parameters.Add("@Exworks", SqlDbType.Decimal).Value = Convert.ToDecimal(Exworks)
        End If
        command.Parameters.Add("@CHG_CURR", SqlDbType.VarChar).Value = CHG_CURR
        command.Parameters.Add("@EXW_CURR", SqlDbType.VarChar).Value = EXW_CURR
        command.Parameters.Add("@JKT_CURR", SqlDbType.VarChar).Value = JKT_CURR
        command.Parameters.Add("@SIN_CURR", SqlDbType.VarChar).Value = SIN_CURR
        command.Parameters.Add("@OTH_CURR", SqlDbType.VarChar).Value = OTH_CURR
        command.Parameters.Add("@PR_CURR4", SqlDbType.VarChar).Value = PR_CURR4
        If (F <> "") Then
            command.Parameters.Add("@F", SqlDbType.Decimal).Value = Convert.ToDecimal(F)
        End If
        If (F1 <> "") Then
            command.Parameters.Add("@F1", SqlDbType.Decimal).Value = Convert.ToDecimal(F1)
        End If
        If (JKT <> "") Then
            command.Parameters.Add("@JKT", SqlDbType.Decimal).Value = Convert.ToDecimal(JKT)
        End If
        If (SIN <> "") Then
            command.Parameters.Add("@SIN", SqlDbType.Decimal).Value = Convert.ToDecimal(SIN)
        End If
        If (OTH <> "") Then
            command.Parameters.Add("@OTH", SqlDbType.Decimal).Value = Convert.ToDecimal(OTH)
        End If
        If (PR <> "") Then
            command.Parameters.Add("@PR", SqlDbType.Decimal).Value = Convert.ToDecimal(PR)
        End If
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function DoPriceCalculation(CurrSell As String, CurrJob As String, CurrBuy As String, PriceBuy As String, _
                                     Cost1Curr As String, Cost2Curr As String, Cost3Curr As String, Cost4Curr As String, _
                                     Cost5Curr As String, ExtCostCurr As String, Cost1Per As String, Cost2Per As String, _
                                     Cost3Per As String, Cost4Per As String, Cost5Per As String, ExtCostPer As String, _
                                     Cost1 As String, Cost2 As String, Cost3 As String, Cost4 As String, Cost5 As String, _
                                     ExtCost As String, F1 As String, F As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_DoPriceCalculation"
        command.Parameters.Add("@CurrSell", SqlDbType.VarChar).Value = CurrSell
        command.Parameters.Add("@CurrJob", SqlDbType.VarChar).Value = CurrJob
        command.Parameters.Add("@CurrBuy", SqlDbType.VarChar).Value = CurrBuy
        If (PriceBuy <> "") Then
            command.Parameters.Add("@PriceBuy", SqlDbType.Real).Value = Convert.ToDecimal(PriceBuy)
        End If
        If (Cost1Curr <> "") Then
            command.Parameters.Add("@Cost1Curr", SqlDbType.VarChar).Value = Convert.ToString(Cost1Curr)
        End If
        If (Cost2Curr <> "") Then
            command.Parameters.Add("@Cost2Curr", SqlDbType.VarChar).Value = Convert.ToString(Cost2Curr)
        End If
        If (Cost3Curr <> "") Then
            command.Parameters.Add("@Cost3Curr", SqlDbType.VarChar).Value = Convert.ToString(Cost3Curr)
        End If
        If (Cost4Curr <> "") Then
            command.Parameters.Add("@Cost4Curr", SqlDbType.VarChar).Value = Convert.ToString(Cost4Curr)
        End If
        If (Cost5Curr <> "") Then
            command.Parameters.Add("@Cost5Curr", SqlDbType.VarChar).Value = Convert.ToString(Cost5Curr)
        End If
        If (ExtCostCurr <> "") Then
            command.Parameters.Add("@ExtCostCurr", SqlDbType.VarChar).Value = Convert.ToString(ExtCostCurr)
        End If
        If (Cost1Per <> "") Then
            command.Parameters.Add("@Cost1Per", SqlDbType.Real).Value = Convert.ToDecimal(Cost1Per)
        End If
        If (Cost2Per <> "") Then
            command.Parameters.Add("@Cost2Per", SqlDbType.Real).Value = Convert.ToDecimal(Cost2Per)
        End If
        If (Cost3Per <> "") Then
            command.Parameters.Add("@Cost3Per", SqlDbType.Real).Value = Convert.ToDecimal(Cost3Per)
        End If
        If (Cost4Per <> "") Then
            command.Parameters.Add("@Cost4Per", SqlDbType.Real).Value = Convert.ToDecimal(Cost4Per)
        End If
        If (Cost5Per <> "") Then
            command.Parameters.Add("@Cost5Per", SqlDbType.Real).Value = Convert.ToDecimal(Cost5Per)
        End If
        If (ExtCostPer <> "") Then
            command.Parameters.Add("@ExtCostPer", SqlDbType.Real).Value = Convert.ToDecimal(ExtCostPer)
        End If
        If (Cost1 <> "") Then
            command.Parameters.Add("@Cost1", SqlDbType.Real).Value = Convert.ToDecimal(Cost1)
        End If
        If (Cost2 <> "") Then
            command.Parameters.Add("@Cost2", SqlDbType.Real).Value = Convert.ToDecimal(Cost2)
        End If
        If (Cost3 <> "") Then
            command.Parameters.Add("@Cost3", SqlDbType.Real).Value = Convert.ToDecimal(Cost3)
        End If
        If (Cost4 <> "") Then
            command.Parameters.Add("@Cost4", SqlDbType.Real).Value = Convert.ToDecimal(Cost4)
        End If
        If (Cost5 <> "") Then
            command.Parameters.Add("@Cost5", SqlDbType.Real).Value = Convert.ToDecimal(Cost5)
        End If
        If (ExtCost <> "") Then
            command.Parameters.Add("@ExtCost", SqlDbType.Real).Value = Convert.ToDecimal(ExtCost)
        End If
        If (F1 <> "") Then
            command.Parameters.Add("@F1", SqlDbType.Real).Value = Convert.ToDecimal(F1)
        End If
        If (F <> "") Then
            command.Parameters.Add("@F", SqlDbType.Real).Value = Convert.ToDecimal(F)
        End If
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function
    Public Function PriceCalculationEntry(groups As String, code As String, principalcode As String, incoterm As String, _
                                          exworks As String, exw_curr As String, pr_code1 As String, percent_0 As String, jkt As String, _
                                          jkt_curr As String, pr_code2 As String, percent_1 As String, sin As String, sin_curr As String, _
                                          pr_code3 As String, percent_2 As String, other_cost As String, oth_curr As String, pr_code4 As String, _
                                          percent_3 As String, pr_cost4 As String, pr_curr4 As String, pr_code5 As String, percent_4 As String, _
                                          pr_cost5 As String, pr_curr5 As String, landed_cost As String, f As String, f1 As String, price As String, _
                                          price1 As String, curr_base As String, uom As String, delivery As String, terms_pay As String, _
                                          status_code As String, note As String, data_entry As String, chg As String, chg_curr As String, _
                                          intcomp As String, TypeAct As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "proc_PriceCalculationEntry"
        command.Parameters.Add("@groups", SqlDbType.VarChar).Value = groups
        command.Parameters.Add("@code", SqlDbType.VarChar).Value = code
        command.Parameters.Add("@principalcode", SqlDbType.VarChar).Value = principalcode
        command.Parameters.Add("@incoterm", SqlDbType.VarChar).Value = incoterm
        command.Parameters.Add("@exworks,", SqlDbType.VarChar).Value = exworks
        command.Parameters.Add("@exw_curr,", SqlDbType.VarChar).Value = exw_curr
        command.Parameters.Add("@pr_code1,", SqlDbType.VarChar).Value = pr_code1
        command.Parameters.Add("@percent_0,", SqlDbType.VarChar).Value = percent_0
        command.Parameters.Add("@jkt,", SqlDbType.VarChar).Value = jkt
        command.Parameters.Add("@jkt_curr,", SqlDbType.VarChar).Value = jkt_curr
        command.Parameters.Add("@pr_code2,", SqlDbType.VarChar).Value = pr_code2
        command.Parameters.Add("@percent_1,", SqlDbType.VarChar).Value = percent_1
        command.Parameters.Add("@sin,", SqlDbType.VarChar).Value = sin
        command.Parameters.Add("@sin_curr,", SqlDbType.VarChar).Value = sin_curr
        command.Parameters.Add("@pr_code3,", SqlDbType.VarChar).Value = pr_code3
        command.Parameters.Add("@percent_2,", SqlDbType.VarChar).Value = percent_2
        command.Parameters.Add("@other_cost,", SqlDbType.VarChar).Value = other_cost
        command.Parameters.Add("@oth_curr,", SqlDbType.VarChar).Value = oth_curr
        command.Parameters.Add("@pr_code4,", SqlDbType.VarChar).Value = pr_code4
        command.Parameters.Add("@percent_3,", SqlDbType.VarChar).Value = percent_3
        command.Parameters.Add("@pr_cost4,", SqlDbType.VarChar).Value = pr_cost4
        command.Parameters.Add("@pr_curr4,", SqlDbType.VarChar).Value = pr_curr4
        command.Parameters.Add("@pr_code5,", SqlDbType.VarChar).Value = pr_code5
        command.Parameters.Add("@percent_4,", SqlDbType.VarChar).Value = percent_4
        command.Parameters.Add("@pr_cost5,", SqlDbType.VarChar).Value = pr_cost5
        command.Parameters.Add("@pr_curr5,", SqlDbType.VarChar).Value = pr_curr5
        command.Parameters.Add("@landed_cost,", SqlDbType.VarChar).Value = landed_cost
        command.Parameters.Add("@f,", SqlDbType.VarChar).Value = f
        command.Parameters.Add("@f1,", SqlDbType.VarChar).Value = f1
        command.Parameters.Add("@price,", SqlDbType.VarChar).Value = price
        command.Parameters.Add("@price1,", SqlDbType.VarChar).Value = price1
        command.Parameters.Add("@curr_base,", SqlDbType.VarChar).Value = curr_base
        command.Parameters.Add("@uom,", SqlDbType.VarChar).Value = uom
        command.Parameters.Add("@delivery,", SqlDbType.VarChar).Value = delivery
        command.Parameters.Add("@terms_pay,", SqlDbType.VarChar).Value = terms_pay
        command.Parameters.Add("@status_code,", SqlDbType.VarChar).Value = status_code
        command.Parameters.Add("@note,", SqlDbType.VarChar).Value = note
        command.Parameters.Add("@data_entry,", SqlDbType.VarChar).Value = data_entry
        command.Parameters.Add("@chg,", SqlDbType.VarChar).Value = chg
        command.Parameters.Add("@chg_curr,", SqlDbType.VarChar).Value = chg_curr
        command.Parameters.Add("@intcomp,", SqlDbType.VarChar).Value = intcomp
        command.Parameters.Add("@Comp", SqlDbType.VarChar).Value = Var.CompID
        command.Parameters.Add("@TypeAct", SqlDbType.VarChar).Value = TypeAct
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function
End Class
